import mysql.connector
from mysql.connector import Error
import os
from dotenv import load_dotenv

# 加载环境变量（安全存储数据库凭据）
load_dotenv()


def get_speed():
    """查询并返回所有产品ID"""
    connection = None
    try:
        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'rm-uf606049v7j2kbbv35o.mysql.rds.aliyuncs.com'),
            user=os.getenv('DB_USER', 'pay'),
            password=os.getenv('DB_PASSWORD', '!@ChMwem6GdiJkq3DN'),
            database=os.getenv('DB_NAME', 'pay'),
            port=os.getenv('DB_PORT', 3109)
        )

        if connection.is_connected():
            print("成功连接到MySQL数据库!")
            # 创建游标
            cursor1 = connection.cursor()

            # 执行查询
            query1 = '''SELECT 
                            SUM(
                                IF(finish_time > 0, 
                                    finish_time - instime, 
                                    UNIX_TIMESTAMP() - instime
                                )
                            ) / COUNT(*) AS avg_seconds,
                            COUNT(*) AS total_records
                        FROM pay_payout_order
                        WHERE 
                            status = 4
                            AND instime <= UNIX_TIMESTAMP() - 300;
                    '''
            cursor1.execute(query1)

            # 获取所有数据
            results1 = cursor1.fetchall()

            minutes = results1[0][0] / results1[0][1] / 60

            if minutes < 1:
                return "快"
                print("快")
            if minutes >= 1 and minutes < 10:
                return "代付速度正常"
                print("代付速度正常")
            if minutes >= 0 and minutes < 30:
                return "代付有延迟，正在加急处理，"
                print("代付有延迟，正在加急处理，")
            if minutes >= 30:
                return "代付速度慢，急单勿提"
                print("代付速度慢，急单勿提")


    except Error as e:
        print(f"数据库错误: {e}")
        return []

    finally:
        # 确保关闭连接
        if connection and connection.is_connected():
            cursor1.close()
            connection.close()
            print("数据库连接已关闭")
